package com.hntxk.cqzd.export.util;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


/**
 * 导出excel工具类
 *
 * @Author: Liuqing
 * @Date: 2020/6/28
 */
public class ExcelUtil {

    /**
     * 导出单行标题excel，多行标题需要根据具体业务修改合并内容，自行实现
     *
     * @param object      导出的数据，该数据只能为单个对象或List
     * @param title       excel标题
     * @param fieldName   数据字段名
     * @param response    HttpServletResponse对象
     * @param xlsFileName 导出文件名
     */
    public static void generateExcelSaveLocal(Object object,
                                              String[] title,
                                              String[] fieldName,
                                              HttpServletResponse response,
                                              String xlsFileName) {
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建一个sheet-test1
        String sheetName = new SimpleDateFormat("yyyyMMdd").format(new Date());
        sheetName += "-device";
        HSSFSheet sheet = wb.createSheet(sheetName);

        //设置单元格风格，居中对齐.
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        cs.setWrapText(true);
        //下边框
        cs.setBorderBottom(BorderStyle.THIN);
        //左边框
        cs.setBorderLeft(BorderStyle.THIN);
        //上边框
        cs.setBorderTop(BorderStyle.THIN);
        //右边框
        cs.setBorderRight(BorderStyle.THIN);

        //设置字体:
        HSSFFont font = wb.createFont();
        font.setFontName("仿宋_GB2312");
        //设置字体大小
        font.setFontHeightInPoints((short) 14);
        //粗体显示
        font.setBold(true);
        cs.setFont(font);

        //创建第一行
        HSSFRow row = sheet.createRow((short) 0);
        HSSFCell cell;

        for (int i = 0; i < title.length; i++) {
            //设置表头-标题
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cs);
        }

        //设置内容风格
        HSSFCellStyle valueCs = wb.createCellStyle();
        valueCs.setAlignment(HorizontalAlignment.CENTER);

        //下边框
        valueCs.setBorderBottom(BorderStyle.THIN);
        //左边框
        valueCs.setBorderLeft(BorderStyle.THIN);
        //上边框
        valueCs.setBorderTop(BorderStyle.THIN);
        //右边框
        valueCs.setBorderRight(BorderStyle.THIN);
        //设置字体:
        HSSFFont valueFont = wb.createFont();
        valueFont.setFontName("黑体");
        //设置字体大小
        valueFont.setFontHeightInPoints((short) 12);
        valueCs.setFont(valueFont);

        //判断导出数据对象类型
        if (object instanceof List) {
            //处理集合数据类型
            listPaddingRow(sheet, valueCs, (List) object, fieldName,1);
        } else {
            //创建第二行
            row = sheet.createRow((short) 1);
            JSONObject jsonObject = (JSONObject) JSONObject.toJSON(object);
            for (int j = 0; j < fieldName.length; j++) {
                cell = row.createCell(j);
                cell.setCellStyle(valueCs);

                if (null != jsonObject.getString(fieldName[j])) {
                    cell.setCellValue(jsonObject.getString(fieldName[j]));
                } else {
                    cell.setCellValue("");
                }
                sheet.autoSizeColumn(j);
            }
        }


        try {
            if (null != response) {
                //页面弹出下载或保存
                response.setContentType("application/x-download");
                //文件名
                String fileName = "";
                //判断文件名是否包含后缀
                if (xlsFileName.contains(".xls") || xlsFileName.contains("xlsx")) {
                    fileName = xlsFileName;
                } else {
                    fileName = xlsFileName + ".xls";
                }

                response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso-8859-1"));
                response.setCharacterEncoding("utf-8");
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.close();
            } else {
                FileOutputStream fileOut = new FileOutputStream("c:/cqzd/" + xlsFileName + ".xls");
                wb.write(fileOut);
                fileOut.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static void listPaddingRow(HSSFSheet sheet, HSSFCellStyle valueCs, List list, String[] fieldName,int incr) {

        //行处理对象
        HSSFRow row = null;
        //列处理对象
        HSSFCell cell = null;
        //处理数据
        if (!CollectionUtils.isEmpty(list)) {
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow((short) i + incr);
                JSONObject jsonObject = (JSONObject) JSONObject.toJSON(list.get(i));
                for (int j = 0; j < fieldName.length; j++) {
                    cell = row.createCell(j);
                    cell.setCellStyle(valueCs);

                    if (null != jsonObject.getString(fieldName[j])) {
                        cell.setCellValue(jsonObject.getString(fieldName[j]));
                    } else {
                        cell.setCellValue("");
                    }
                    sheet.autoSizeColumn(j);
                }
            }
        }
    }

    /**
     * 导出单行标题excel，多行标题需要根据具体业务修改合并内容，自行实现
     *
     * @param object      导出的数据，该数据只能为单个对象或List
     * @param title1      一行excel标题
     * @param title2      二行excel标题
     * @param mergedRegion 需要合并的单元格位置 从0开始 例如合并第0个单元格到第3个单元格，在合并到第7个单元格 入参为 0，3，7
     * @param fieldName   数据字段名
     * @param response    HttpServletResponse对象
     * @param xlsFileName 导出文件名
     */
    public static void generateExcelSaveLocal(Object object,
                                              String[] title1,
                                              String[] title2,
                                              int[] mergedRegion,
                                              String[] fieldName,
                                              HttpServletResponse response,
                                              String xlsFileName){
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建一个sheet-test1
        String sheetName = new SimpleDateFormat("yyyyMMdd").format(new Date());
        sheetName += "-device";
        HSSFSheet sheet = wb.createSheet(sheetName);

        //设置单元格风格，居中对齐.
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        cs.setWrapText(true);
        //下边框
        cs.setBorderBottom(BorderStyle.THIN);
        //左边框
        cs.setBorderLeft(BorderStyle.THIN);
        //上边框
        cs.setBorderTop(BorderStyle.THIN);
        //右边框
        cs.setBorderRight(BorderStyle.THIN);

        //设置字体:
        HSSFFont font = wb.createFont();
        font.setFontName("仿宋_GB2312");
        //设置字体大小
        font.setFontHeightInPoints((short) 14);
        //粗体显示
        font.setBold(true);
        cs.setFont(font);

        //创建第一行
        HSSFRow row = sheet.createRow((short) 0);
        HSSFCell cell;

        for (int i = 0; i < mergedRegion.length-1; i++) {
            int firstColl = 0;
            if (i == 0){
                firstColl = mergedRegion[i];
            }else {
                firstColl = mergedRegion[i] + 1;
            }
            sheet.addMergedRegion(new CellRangeAddress(0,0,firstColl,mergedRegion[i+1]));
            //设置表头-标题第一行
            cell = row.createCell(firstColl);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(cs);
        }

        //创建第二行
        row = sheet.createRow((short) 1);

        for (int i = 0; i < title2.length; i++) {
            //设置表头-标题第二行
            cell = row.createCell(i);
            cell.setCellValue(title2[i]);
            cell.setCellStyle(cs);
        }

        //设置内容风格
        HSSFCellStyle valueCs = wb.createCellStyle();
        valueCs.setAlignment(HorizontalAlignment.CENTER);

        //下边框
        valueCs.setBorderBottom(BorderStyle.THIN);
        //左边框
        valueCs.setBorderLeft(BorderStyle.THIN);
        //上边框
        valueCs.setBorderTop(BorderStyle.THIN);
        //右边框
        valueCs.setBorderRight(BorderStyle.THIN);
        //设置字体:
        HSSFFont valueFont = wb.createFont();
        valueFont.setFontName("黑体");
        //设置字体大小
        valueFont.setFontHeightInPoints((short) 12);
        valueCs.setFont(valueFont);

        //判断导出数据对象类型
        if (object instanceof List) {
            //处理集合数据类型
            listPaddingRow(sheet, valueCs, (List) object, fieldName,2);
        } else {
            //创建第三行
            row = sheet.createRow((short) 2);
            JSONObject jsonObject = (JSONObject) JSONObject.toJSON(object);
            for (int j = 0; j < fieldName.length; j++) {
                cell = row.createCell(j);
                cell.setCellStyle(valueCs);

                if (null != jsonObject.getString(fieldName[j])) {
                    cell.setCellValue(jsonObject.getString(fieldName[j]));
                } else {
                    cell.setCellValue("");
                }
                sheet.autoSizeColumn(j);
            }
        }


        try {
            if (null != response) {
                //页面弹出下载或保存
                response.setContentType("application/x-download");
                //文件名
                String fileName = "";
                //判断文件名是否包含后缀
                if (xlsFileName.contains(".xls") || xlsFileName.contains("xlsx")) {
                    fileName = xlsFileName;
                } else {
                    fileName = xlsFileName + ".xls";
                }

                response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso-8859-1"));
                response.setCharacterEncoding("utf-8");
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.close();
            } else {
                FileOutputStream fileOut = new FileOutputStream("c:/cqzd/" + xlsFileName + ".xls");
                wb.write(fileOut);
                fileOut.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}